﻿Imports System.Data
Imports System.Data.SqlClient
Public Class VeiculosDALTeste


    Sub New()

    End Sub

    Function inserir(ByVal MOD_CODIGO As Integer, ByVal COM_CODIGO As Integer, ByVal VEI_PLACA As Char,
                     ByVal VEI_CHASSI As String, ByVal VEI_COR As String, ByVal VEI_CATEGORIA As String,
                     ByVal VEI_CAPACIDADE As Integer, ByVal VEI_ANO_FABRICACAO As Integer,
                     ByVal VEI_ANO_MODELO As Integer, ByVal VEI_ATIVO_SN As Char, ByVal VEI_DATA_CADASTRO As Date) As Integer

        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim codigo As Integer
        Try
            cmd.CommandText = "INSERT INTO VEICULOS (MOD_CODIGO, COM_CODIGO, VEI_PLACA, " & _
                              "VEI_CHASSI, VEI_COR, VEI_CATEGORIA, VEI_CAPACIDADE, VEI_ANO_FABRICACAO, " & _
                              "VEI_ANO_MODELO, VEI_ATIVO_SN, VEI_DATA_CADASTRO) " & _
                              "VALUES (@MOD_CODIGO, @COM_CODIGO, @VEI_PLACA, " & _
                              "@VEI_CHASSI, @VEI_COR, @VEI_CATEGORIA, @VEI_CAPACIDADE, @VEI_ANO_FABRICACAO, " & _
                              "@VEI_ANO_MODELO, @VEI_ATIVO_SN, @VEI_DATA_CADASTRO)"

            cmd.Parameters.Add(New SqlParameter("@MOD_CODIGO", SqlDbType.Int)).Value = MOD_CODIGO
            cmd.Parameters.Add(New SqlParameter("@COM_CODIGO", SqlDbType.Int)).Value = COM_CODIGO
            cmd.Parameters.Add(New SqlParameter("@VEI_PLACA", SqlDbType.Char, 8)).Value = VEI_PLACA
            cmd.Parameters.Add(New SqlParameter("@VEI_CHASSI", SqlDbType.VarChar, 50)).Value = VEI_CHASSI
            cmd.Parameters.Add(New SqlParameter("@VEI_COR", SqlDbType.VarChar, 25)).Value = VEI_COR
            cmd.Parameters.Add(New SqlParameter("@VEI_CATEGORIA", SqlDbType.VarChar, 30)).Value = VEI_CATEGORIA
            cmd.Parameters.Add(New SqlParameter("@VEI_CAPACIDADE", SqlDbType.Int)).Value = VEI_CAPACIDADE
            cmd.Parameters.Add(New SqlParameter("@VEI_ANO_FABRICACAO", SqlDbType.Int)).Value = VEI_ANO_FABRICACAO
            cmd.Parameters.Add(New SqlParameter("@VEI_ANO_MODELO", SqlDbType.Int)).Value = VEI_ANO_MODELO
            cmd.Parameters.Add(New SqlParameter("@VEI_ATIVO_SN", SqlDbType.Char, 1)).Value = VEI_ATIVO_SN
            cmd.Parameters.Add(New SqlParameter("@VEI_DATA_CADASTRO", SqlDbType.Date)).Value = VEI_DATA_CADASTRO

            codigo = objD.executacomandoAI(cmd, "VEICULOS")
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return codigo
    End Function

    Sub alterar(ByVal VEI_CODIGO As Integer, ByVal MOD_CODIGO As Integer, ByVal COM_CODIGO As Integer, ByVal VEI_PLACA As Char,
                     ByVal VEI_CHASSI As String, ByVal VEI_COR As String, ByVal VEI_CATEGORIA As String,
                     ByVal VEI_CAPACIDADE As Integer, ByVal VEI_ANO_FABRICACAO As Integer,
                     ByVal VEI_ANO_MODELO As Integer, ByVal VEI_ATIVO_SN As Char, ByVal VEI_DATA_CADASTRO As Date)
        Dim cmd As New SqlCommand
        Dim objD As New Dados

        Try
            cmd.CommandText = "UPDATE VEICULOS SET MOD_CODIGO=@MOD_CODIGO, COM_CODIGO=@COM_CODIGO, VEI_PLACA=@VEI_PLACA, " +
                              "VEI_CHASSI=@VEI_CHASSI, VEI_COR=@VEI_COR, VEI_CATEGORIA=@VEI_CATEGORIA, VEI_CAPACIDADE=@VEI_CAPACIDADE, " +
                              "VEI_ANO_FABRICACAO=@VEI_ANO_FABRICACAO, VEI_ANO_MODELO=@VEI_ANO_MODELO, VEI_ATIVO_SN=@VEI_ATIVO_SN, " +
                              "VEI_DATA_CADASTRO=@VEI_DATA_CADASTRO WHERE VEI_CODIGO=@VEI_CODIGO"

            cmd.Parameters.Add(New SqlParameter("@VEI_CODIGO", SqlDbType.Int)).Value = VEI_CODIGO
            cmd.Parameters.Add(New SqlParameter("@MOD_CODIGO", SqlDbType.Int)).Value = MOD_CODIGO
            cmd.Parameters.Add(New SqlParameter("@COM_CODIGO", SqlDbType.Int)).Value = COM_CODIGO
            cmd.Parameters.Add(New SqlParameter("@VEI_PLACA", SqlDbType.Char, 8)).Value = VEI_PLACA
            cmd.Parameters.Add(New SqlParameter("@VEI_CHASSI", SqlDbType.VarChar, 50)).Value = VEI_CHASSI
            cmd.Parameters.Add(New SqlParameter("@VEI_COR", SqlDbType.VarChar, 25)).Value = VEI_COR
            cmd.Parameters.Add(New SqlParameter("@VEI_CATEGORIA", SqlDbType.VarChar, 30)).Value = VEI_CATEGORIA
            cmd.Parameters.Add(New SqlParameter("@VEI_CAPACIDADE", SqlDbType.Int)).Value = VEI_CAPACIDADE
            cmd.Parameters.Add(New SqlParameter("@VEI_ANO_FABRICACAO", SqlDbType.Int)).Value = VEI_ANO_FABRICACAO
            cmd.Parameters.Add(New SqlParameter("@VEI_ANO_MODELO", SqlDbType.Int)).Value = VEI_ANO_MODELO
            cmd.Parameters.Add(New SqlParameter("@VEI_ATIVO_SN", SqlDbType.Char, 1)).Value = VEI_ATIVO_SN
            cmd.Parameters.Add(New SqlParameter("@VEI_DATA_CADASTRO", SqlDbType.Date)).Value = VEI_DATA_CADASTRO

            objD.executacomando(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub

    Sub excluir(ByVal VEI_CODIGO As Integer)
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Try
            cmd.CommandText = ""
            cmd.Parameters.Add(New SqlParameter("@VEI_CODIGO", SqlDbType.Int)).Value = VEI_CODIGO
            objD.executacomando(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub

    Function localizar(ByVal VEI_CODIGO As Integer) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable = Nothing
        Try

            cmd.CommandText = "SELECT * FROM VEICULOS WHERE VEI_CODIGO = @VEI_CODIGO"


            cmd.Parameters.Add(New SqlParameter("@VEI_CODIGO", SqlDbType.Int)).Value = VEI_CODIGO

            tab = objD.executaConsulta(cmd)

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        tab = Nothing
    End Function

    Function localizar(ByVal campo As String, ByVal condicao As String) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable = Nothing
        Try

            If (condicao <> "") Then
                cmd.CommandText = "SELECT * FROM VEICULOS WHERE " + campo + " =@CONDICAO"
            Else
                cmd.CommandText = "SELECT * FROM VEICULOS WHERE VEI_CODIGO = @VEI_CODIGO"
            End If

            cmd.Parameters.Add(New SqlParameter("@CONDICAO", SqlDbType.Int)).Value = condicao

            tab = objD.executaConsulta(cmd)

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        tab = Nothing
    End Function

    Function localizar(ByVal descricao As String) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable
        Try

            cmd.CommandText = "SELECT * FROM VEICULOS WHERE vei_placa like @descricao"
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = descricao + "%"
            tab = objD.executaConsulta(cmd)

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function


    Function localizarProxAnterior(ByVal DESCRICAO As String, ByVal VEI_CODIGO As Integer) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable = Nothing
        Try
            If (DESCRICAO = "proximo") Then
                cmd.CommandText = "SELECT * FROM VEICULOS WHERE VEI_CODIGO = (SELECT MIN(VEI_CODIGO) FROM VEICULOS WHERE VEI > @VEI_CODIGO)"
            ElseIf (DESCRICAO = "anterior") Then
                cmd.CommandText = "SELECT * FROM VEICULOS WHERE VEI_CODIGO = (SELECT MAX(VEI_CODIGO) FROM VEICULOS WHERE VEI_CODIGO < @VEI_CODIGO)"
            End If
            'Parâmetro de entrada
            cmd.Parameters.Add(New SqlParameter("@VEI_CODIGO", SqlDbType.Int)).Value = VEI_CODIGO
            'Execução do comando
            tab = objD.executaConsulta(cmd)

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        tab = Nothing
    End Function

End Class
